package com.tony.jdbc.dynamic.controller;

import com.tony.jdbc.dynamic.annotation.DynamicDatasource;
import com.tony.jdbc.dynamic.service.IDynamicAnnotationService;
import com.tony.jdbc.dynamic.service.IDynamicService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author tony
 * @desc 多数据源测试controller
 * @date 2025/11/12 08:51
 */
@RestController
@RequestMapping("base")
@DynamicDatasource("h2-1")
public class DynamicDatasourceController {
    @Autowired
    JdbcTemplate defalutJdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate defaultNamedJdbcTemplate;
    @Autowired
    IDynamicService dynamicService;
    @Autowired
    IDynamicAnnotationService dynamicAnnotationService;

    @PostConstruct
    public void init() {
        //初始化数据源
        defalutJdbcTemplate.execute("DROP TABLE IF EXISTS t_user");
        defalutJdbcTemplate.execute("CREATE TABLE t_user(id varchar(225) not null,user_name varchar(225) not null,pwd varchar(225) not null,age int)");
        defalutJdbcTemplate.execute("INSERT INTO t_user VALUES ('0', '张三h2','zs20', 20)" +
                ",('1', '李四h2','ls30' ,30)" +
                ",('2', '王五h2', 'wu40',40)");
        defalutJdbcTemplate.execute("DROP TABLE IF EXISTS t_datasource;");
        defalutJdbcTemplate.execute("CREATE TABLE t_datasource" +
                "(" +
                "id varchar(225) not null," +
                "driver_class_name varchar(225) not null," +
                "url varchar(225) not null," +
                "username  varchar(225) not null," +
                "password varchar(225) not null" +
                ")");
        defalutJdbcTemplate.execute("INSERT INTO t_datasource VALUES ('1', 'org.apache.derby.jdbc.EmbeddedDriver', 'jdbc:derby:derbyDb;create=true','derby','derby')");

        dynamicService.executeJdbc("derby-1", jdbcTemplate -> {
            Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM SYS.SYSTABLES WHERE TABLENAME = 'T_USER_DERBY'", Integer.class);
            if (count != null && 0 != count) {
                jdbcTemplate.execute("drop TABLE T_USER_DERBY");
            }
            //初始化数据源
            jdbcTemplate.execute("CREATE TABLE T_USER_DERBY(id varchar(225) not null,user_name varchar(225) not null,pwd varchar(225) not null,age int)");
            jdbcTemplate.execute("INSERT INTO T_USER_DERBY VALUES ('0', '张三derby','zs20', 20)" +
                    ",('1', '李四derby','ls30' ,30)" +
                    ",('2', '王五derby', 'wu40',40)");
        });
    }

    /**
     * 从默认数据源获取数据源信息
     *
     * @return 所有数据源
     */
    @GetMapping("getAllDatasource")
    public List<DataSourceProperties> getAllDatasource() {
        return defaultNamedJdbcTemplate.query("select id,driver_class_name,url,username,password from t_datasource", new HashMap<>(), new BeanPropertyRowMapper<>(DataSourceProperties.class));
    }

    /**
     * 通过数据源名称查询数据源下的用户表数据
     *
     * @param name 数据源名称
     * @return 获取数据源下的用户表数据
     */
    @GetMapping("getByDatasourceName")
    public String getAllDatasource(String name) throws SQLException {
        return dynamicService.executeJdbcWithResult(name, jdbcTemplate -> {
            try {
                return getDatabaseProduceName(jdbcTemplate);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });


    }

    /**
     * 注解方式切换数据源，查询h2数据源下的用户表数据
     *
     * @return h2数据源下的用户表数据
     */
    @GetMapping("getByAnnotationH2")
    public List<Map<String, Object>> getDataSourceH2() throws SQLException {
        return dynamicAnnotationService.getDataSourceH2();
    }

    /**
     * 注解方式切换数据源，查询derby数据源下的用户表数据
     *
     * @return derby数据源下的用户表数据
     */
    @GetMapping("getByAnnotationDerby")
    public List<Map<String, Object>> getDataSourceDerby() throws SQLException {
        return dynamicAnnotationService.getDataSourceDerby();
    }


    /**
     * jta异常测试，返回数据源下的用户表数据
     * 如果是jta会返回3条数据，否则返回4条数据
     *
     * @return derby数据源下的用户表数据
     */
    @GetMapping("testTransaction")
    public List<Map<String, Object>> testTransaction() throws SQLException {
        try {
            //executeJdbc方法带事务执行
            dynamicService.executeJdbc("h2-1", jdbcTemplate -> {
                jdbcTemplate.execute("INSERT INTO t_user VALUES ('3', '张三3-h2','zs20', 20)");
                dynamicService.executeJdbc("derby-1", jdbcTemplate1 -> {
                    //如果没有jta，获取的数据源连接还是h2的，会T_USER_DERBY表不存在错，org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "T_USER_DERBY" not found; SQL statement:
                    jdbcTemplate1.execute("INSERT INTO T_USER_DERBY VALUES ('3', '张三3-derby','zs20',20)");
                });
                //模拟异常
                //jdbcTemplate.execute("INSERT INTO t_user VALUES ('4', '张三4','zs20', 20");
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
        //开启jta时，访问一次增加一条数据，非jta时：T_USER_DERBY表不存在，插入异常回滚，返回3条数据
        return dynamicService.executeJdbcWithResult("derby-1", jdbcTemplate -> {
            return jdbcTemplate.queryForList(("select id,user_name,pwd,age from T_USER_DERBY"));
        });
    }

    /**
     * 获取数据库产品名称
     *
     * @return 数据库产品名称
     */
    private String getDatabaseProduceName(JdbcTemplate jdbcTemplate) throws SQLException {
        String databaseProductName = null;
        Connection connection = null;
        //通过DataSourceUtils里的getConnection获取连接，doReleaseConnection释放连接，避免事务下数据源连接被关闭
        try {
            connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
            databaseProductName = connection.getMetaData().getDatabaseProductName();
        } finally {
            if (connection != null) {
                DataSourceUtils.doReleaseConnection(connection, jdbcTemplate.getDataSource());
            }
        }
        return databaseProductName;
    }
}
